package com.bdjbd.service.common.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.bdjbd.Message;
import com.bdjbd.bo.ErrorItem;
import com.bdjbd.bo.ImportResultVO;
import com.bdjbd.dao.entity.BaseParameter;
import com.bdjbd.dao.entity.BaseSheetMatch;
import com.bdjbd.dao.entity.BaseSimpleDefinition;
import com.bdjbd.dao.entity.QaStandardRecord;
import com.bdjbd.dao.mapper.BaseParameterMapper;
import com.bdjbd.dao.mapper.BaseSheetMatchMapper;
import com.bdjbd.dao.mapper.BaseSimpleDefinitionMapper;
import com.bdjbd.dao.mapper.QaStandardRecordMapper;
import com.bdjbd.service.BaseSimpleDefinitionService;
import com.bdjbd.service.QaStandardRecordItemService;
import com.bdjbd.service.QaStandardRecordService;
import com.bdjbd.service.common.ExcelService;
import com.bdjbd.util.FileUtils;
import com.fasterxml.jackson.databind.ser.std.StdKeySerializers.Default;

import lombok.extern.slf4j.Slf4j;

/**
 * @author shujukuss
 * @version 1.0
 * @className ExcelServiceImpl
 * @description
 * @date 2020/12/22
 **/
@Slf4j
@Service
public class ExcelServiceImpl implements ExcelService {

	@Autowired
	private QaStandardRecordService qaStandardRecordService;

	@Autowired
	private BaseSheetMatchMapper baseSheetMatchMapper;

	@Autowired
	private BaseParameterMapper baseParameterMapper;

	@Autowired
	private BaseSimpleDefinitionService baseSimpleDefinitionService;
	
	@Autowired
	private QaStandardRecordMapper qaStandardRecordMapper;
	
	@Autowired
	private QaStandardRecordItemService qaStandardRecordItemService;
	
	@Value("${file.upload.path}")
    private String filePath;

	@Override
	public Message deal(String userId, File excelFile) {
		ImportResultVO vo = new ImportResultVO();
		FileInputStream is = null;
		try {
			is = new FileInputStream(excelFile);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return Message.error("上传失败");
		}
		if (!excelFile.getName().endsWith(".xls") && !excelFile.getName().endsWith(".xlsx")) {
			FileUtils.delteTempFile(excelFile);
			return Message.error("导入文件非excel类型文件");
		}
		Workbook book = null;
		try {
			book = getWorkbook(excelFile, is);
		} catch (IOException e) {
			e.printStackTrace();
			return Message.error("文件解析失败");
		}
		// 第一个sheet
		Sheet sheet = book.getSheetAt(0);
		// 行数
		int rowNum = sheet.getLastRowNum();
		// 通过第一列的表头数据获取属性
		Row firstRow = sheet.getRow(0);
		List<QaStandardRecord> result = new ArrayList<QaStandardRecord>();
		List<Integer> rowList = new ArrayList<Integer>();
		Map<String, String> map = this.getTitleMap();
		// 错误列表
		List<ErrorItem> errors = new ArrayList<>();
		for (int i = 1; i <= rowNum; i++) {
			Row row = sheet.getRow(i);
			QaStandardRecord record = new QaStandardRecord();
			// title
			List<String> attrList = new ArrayList<>();
			for (int j = 0; j < firstRow.getLastCellNum(); j++) {
				String title = firstRow.getCell(j).getStringCellValue();
				if("".equals(title)) {
					continue;
				}
				String pid = map.get(title);
				String value = null;
				try {
					value = getString(row.getCell(j));
				} catch (Exception e) {
					log.info("title:" + title + ",pid:" + pid + ",row:" + row + ",num:" + j);
					e.printStackTrace();
				}
				if(value != null) {
					value = value.replace(" ", "");
				}
				String attr = this.rule(pid, value);
				if (attr == null) {
					record = this.setValue(record, title, value, pid);
				} else {
					attrList.add(attr);
				}
			}
			if (attrList.size() == 0) {
				result.add(record);
				rowList.add(i);
			} else {
				ErrorItem item = new ErrorItem();
				item.setName(record.getName());
				item.setAttr(attrList);
				if(record.getIdCard() == null||"".equals(record.getIdCard())) {
					continue;
				}
				errors.add(item);
			}

		}

		// 库中未识别列表
		List<String> unrecognizeds = new ArrayList<>();
		// 库中未识别行
		List<Integer> unList = new ArrayList<>();
		List<QaStandardRecord> items = new ArrayList<>();
		List<BaseSimpleDefinition> bsList = baseSimpleDefinitionService.findList("2010007007");
		Map<String, String> bsMap = new HashedMap<>();
		if(bsList != null) {
			for(BaseSimpleDefinition bs : bsList) {
				bsMap.put(bs.getName(), bs.getAttr2());
			}
		}
		for (int k = 0; k < result.size(); k++) {
			// 校验idCard
			String idCard = result.get(k).getIdCard();
			if(idCard == null||"".equals(idCard)) {
				continue;
			}
			QaStandardRecord db = qaStandardRecordService.findByIdCard(idCard);
			if (db == null) {
				unrecognizeds.add(result.get(k).getName());
				unList.add(rowList.get(k));
				continue;
			}
			// 专业技术职务特殊处理
			String technologyTitle = db.getTechnologyTitle();
			String technologyTitle1 = result.get(k).getTechnologyTitle();
			if(!bsMap.get(technologyTitle).equals(bsMap.get(technologyTitle1))) {
				ErrorItem item = new ErrorItem();
				item.setName(result.get(k).getName());
				List<String> attrList = new ArrayList<>();
				attrList.add("现专业技术职务");
				item.setAttr(attrList);
				errors.add(item);
			}
						
			QaStandardRecord r = result.get(k);
			r.setId(db.getId());
			r.setCommitStatus(db.getCommitStatus());
            r.setModifyDate(new Date());
            r.setUserId(db.getUserId());
    		// 技职类别
			String technologyCategory = r.getTechnologyCategory();
			String attr3 = db.getAttr3();
			if (attr3 != null) {
				switch (technologyCategory) {
				case "初职":
					attr3 = attr3.substring(0, attr3.length() - 1) + "4";
					break;
				case "中职":
					attr3 = attr3.substring(0, attr3.length() - 1) + "3";
					break;
				case "副高职":
					attr3 = attr3.substring(0, attr3.length() - 1) + "2";
					break;
				case "正高职":
					attr3 = attr3.substring(0, attr3.length() - 1) + "1";
					break;
				}
			}
			
            r.setAttr3(attr3);
            items.add(r);
            qaStandardRecordItemService.saveOrUpdate(r);
		}
		if(items != null&&items.size() > 0) {
			qaStandardRecordMapper.batchUpdate(items);
		}		
		
		String targetUrl = null;
		if(unrecognizeds.size() > 0) {
			// 创建工作薄
	        XSSFWorkbook workbook = new XSSFWorkbook();
	        // 创建工作表
	        XSSFSheet sheet1 = workbook.createSheet("未识别数据");
	        XSSFRow sheetRow0 = sheet1.createRow(0);
	        for(int j = 0; j < firstRow.getLastCellNum(); j++) {
	    		XSSFCell cell = sheetRow0.createCell(j);
	    		cell.setCellValue(firstRow.getCell(j).getStringCellValue());
	    		cell.setCellType(firstRow.getCell(j).getCellType());
	    	}
	        for(int i = 0; i < unList.size(); i++) {
	        	XSSFRow sheetRow = sheet1.createRow(i + 1);         	
	        	Row row = sheet.getRow(unList.get(i));
	        	for(int j = 0; j < row.getLastCellNum(); j++) {
	        		XSSFCell cell = sheetRow.createCell(j);
	        		cell.setCellValue(row.getCell(j).getStringCellValue());
	        		cell.setCellType(row.getCell(j).getCellType());
	        	}
	        }
	      //写入文件
	        targetUrl = UUID.randomUUID()+ ".xlsx";
	        File file = new File(filePath + targetUrl);
	        if (!file.getParentFile().exists()) {
	            try {
	            	file.getParentFile().mkdirs();
	            	file.createNewFile();
	            } catch (IOException e) {
	                e.printStackTrace();
	            }
	        }
	        try {
	        	workbook.write(new FileOutputStream(file));
	        	workbook.close();
	        } catch (Exception ex) {
	        	System.out.println(ex.getMessage());
	        }		
		}
		
		vo.setErrors(errors);
		vo.setUnrecognizeds(unrecognizeds);		
		
		vo.setTargetUrl(targetUrl);
		return Message.success(vo);
	}

	private String getString(Cell cell) {
		if(cell == null ||cell.getCellType() ==null){
			return null;
		}
		cell.setCellType(CellType.STRING);
		switch(cell.getCellType().getCode()) {
			case 0:
				return String.valueOf(cell.getNumericCellValue());
			case 1:
				return cell.getStringCellValue();
			case 2:
				return String.valueOf(cell.getNumericCellValue());
			case 3:
				return null;
			case 4:
				return String.valueOf(cell.getBooleanCellValue());
			default:
				return null;
		}
	}

	private Workbook getWorkbook(File excelFile, FileInputStream is) throws IOException {
		Workbook book;
		try {
			book = new HSSFWorkbook(is);
		} catch (Exception ex) {
			// 解决read error异常
			is = new FileInputStream(excelFile);
			book = new XSSFWorkbook(is);
		}
		return book;
	}

	/**
	 * @return Map<title,pid>
	 */
	private Map<String, String> getTitleMap() {
		Map<String, String> map = new HashedMap<>(1 << 6);
		List<BaseSheetMatch> list = baseSheetMatchMapper.findAll();
		for (BaseSheetMatch base : list) {
			map.put(base.getTitle(), base.getPid());
		}
		return map;
	}

	private QaStandardRecord setValue(QaStandardRecord record, String title, String value, String pid) {
		if(pid == null||value == null||"".equals(value)) {
			return record;
		}
		switch (pid) {
		case "10100-001":
			record.setName(value);
			break;
		case "10100-002":
			record.setSex("男".equals(value) ? 1L : 0);
			break;
		case "10100-003":
			record.setIdCard(value);
			break;
		case "10100-004":
			record.setPersonnelCategory(value);
			break;
		case "10100-005":
			record.setBirth(this.dateParseYM(value));
			break;
		case "10100-006":
			record.setEnlistmentTime(this.dateParseYM(value));
			break;
		case "10100-008":
			record.setPoliticalAffiliation(value);
			break;
		case "10100-009":
			record.setCaucusTime(this.dateParseYM(value));
			break;
		case "10100-010":
			record.setLastEducation(value);
			break;
		case "10100-011":
			record.setHighestGetTime(this.dateParseYM(value));
			break;
		case "10100-012":
			record.setHighestDegree(value);
			break;
		case "10100-013":
			record.setHighestGetTime(this.dateParseYM(value));
			break;
		case "10100-014":
			record.setHighestSchoolMajor(value);
			break;
		case "10100-015":
			record.setLastSchoolMajor(value);
			break;
		case "10100-016":
			record.setGraduateTime(this.dateParseYM(value));
			break;
		case "10100-017":
			record.setSchoolCategory(value);
			break;
		case "10100-018":
			record.setTechnologyTitle(value);
			break;
		case "10100-019":
			record.setAppointTime(this.dateParseYM(value));
			break;
		case "10100-020":
			record.setTechnologyLevel(value);
			break;
		case "10100-021":
			record.setTechnologyLevelTime(this.dateParseYM(value));
			break;
		case "10100-022":
			record.setMilitaryRank(value);
			break;
		case "10100-023":
			record.setMilitaryRankTime(this.dateParseYM(value));
			break;
		case "10100-024":
			record.setTechnologyCategory(value);
			break;
		case "10100-025":
			record.setTechnologyCategoryTime(this.dateParseYM(value));
			break;
		case "10100-026":
			record.setOfficialRank(value);
			break;
		case "10100-028":
			record.setCategoryType(value);
			break;
		case "10100-029":
			record.setAttr0(value);
			break;
		case "10100-033":
			record.setAttr6(value);
			break;
		case "10100-034":
			record.setAttr7(value);
			break;
		case "10100-032":
			record.setLastTechnologyCategoryTime(this.dateParseYM(value));
			break;
		default:
			break;
		}
		return record;
	}

	private Date dateParseYM(String date) {
		if(date == null) {
			return null;
		}
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd");
		try {
			return sdf.parse(date);
		} catch (ParseException e) {
			SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy.MM");
			try {
				return sdf1.parse(date);
			} catch (ParseException e1) {
				return null;
			}
		}
	}

	/**
	 * 不符合规则的返回字段名
	 * 
	 * @param pid
	 * @param value
	 * @return
	 */
	private String rule(String pid, String value) {		
		BaseParameter baseParameter = baseParameterMapper.selectByPrimaryKey(pid);
		if(baseParameter == null) {
			return null;
		}
		String name = baseParameter.getName();
		String typeValidate = baseParameter.getTypeValidate();
		String type = baseParameter.getType();
		if ("select".equals(type)||"date".equals(type)){
			if(value == null||"".equals(value)) {
				return name;
			}
		}		
		if ("default".equals(typeValidate) || "name".equals(typeValidate)) {
			return null;
		}
		if ("yyyy.MM".equals(typeValidate)) {
			if (dateParseYM(value) == null) {
				return name;
			}
			return null;
		}
		List<BaseSimpleDefinition> list = baseSimpleDefinitionService.findList(typeValidate);
		if (list == null || list.size() == 0) {
			return name;
		}
		Set<String> set = new HashSet<>();
		for (BaseSimpleDefinition base : list) {
			set.add(base.getName());
		}
		if (set.contains(value)) {
			return null;
		}
		return name;
	}

}
